from sentinelsat import SentinelAPI
from xml.dom.minidom import parse
from queue import Queue
import threadpool
import pathlib
import psycopg2
import datetime
import zipfile
import copy
import re
import os

account_infos = [
    # ["fyy001", "show1234"],
    ["fyy_01", "show1234"],
    ["fyy_02", "show1234"],
    ["fyy_03", "show1234"],
    ["fyy_04", "show1234"],
    ["wyq01", "show970213"],
    ["wyq02", "show970213"],
    ["wyq03", "show970213"],
    ["wyq04", "show970213"],
    ["wyq05", "show970213"],
    ["wyq06", "show970213"],
    ["wyq07", "show970213"],
    ["hxx2021", "hxx2021123"],
    ["hxx2022", "hxx2022123"],
    ["hxx20221", "hxx2022123"],
    ["hxx20222", "hxx20222123"],
]
account_count = len(account_infos)
account_queue = Queue(maxsize=account_count)
for i in range(account_count):
	account_queue.put(i,block=True)

# 获取1c含云量
def read_cloud(zipFilePath):
	""""""
	xmlFilePathRe = "^S2.*\.SAFE/MTD_MSIL1C\.xml$"
	zipFile = zipfile.ZipFile(zipFilePath, "r")
	zipFileInfos = zipFile.infolist()
	for zipFileInfo in zipFileInfos:
		if re.match(xmlFilePathRe, zipFileInfo.filename):
			xmlFilePath = zipFile.open(zipFileInfo.filename, "r")
			domTree = parse(xmlFilePath)
			collection = domTree.documentElement
			cloudPercentElement = collection.getElementsByTagName("Cloud_Coverage_Assessment")[0]
			cloudPercent = round(float(copy.deepcopy(cloudPercentElement.childNodes[0].data)))
	return cloudPercent

# 主动入库
def zip_import_sql(path):
    folder = r"/home/遥感技术部/遥感数据/Sentinel2L1cZip"
    name = os.path.basename(path)
    size = os.path.getsize(path)
    size = round(size/(1024*1024),2)
    infos = os.path.splitext(name)[0].split("_")
    shot = datetime.datetime.strptime(infos[2], "%Y%m%dT%H%M%S")
    load = datetime.datetime.strptime(infos[6], "%Y%m%dT%H%M%S")
    tile = infos[5][1:]
    cloud = read_cloud(path)
    date = shot.strftime("%Y%m%d")
    path = str(pathlib.PurePosixPath(folder, date, name))
    sql = f'''
            INSERT INTO l1c_zip_info (name, level, clouds, tile, size, path, shot_date, load_date) 
            VALUES ('{name}', 'L1C', {cloud}, '{tile}', {size}, '{path}', '{shot}', '{load}')
            on conflict (path) do nothing
        '''   # 字段唯一值冲突则不更新
    con = psycopg2.connect(
        database="sentinel2",
        user="postgres",
        password="12345",
        host="192.168.2.172",
        port="5432"
    )
    cur = con.cursor()	
    cur.execute(sql)
    con.commit()
    cur.close()
    con.close()
    return 

# 清临时数据
def delete_incomplete(args):
	[tiles, start_date, end_date, save_folder] = args
	start_date = datetime.datetime.strptime(start_date,"%Y-%m-%d")
	end_date = datetime.datetime.strptime(end_date,"%Y-%m-%d")
	temp_date = start_date
	dates = []
	while temp_date < (end_date+datetime.timedelta(days=1)):
		dates.append(temp_date.strftime("%Y%m%d"))
		temp_date = temp_date + datetime.timedelta(days=1)
	datestr = "|".join(dates)
	tilestr = "|".join(tiles)
	name_re = f"^S2.*_({datestr})T.*_T({tilestr})_.*\.zip\.incomplete$"
	for name in os.listdir(save_folder):
		if re.match(name_re,name):
			os.remove(os.path.join(save_folder, name))
	return

<<<<<<< HEAD
# download sentinel2 data
def sentinel2_downloader(args):
    [tile,date,max_cloud,max_attempts]=args
    date_name = datetime.datetime.strptime(date,"%Y-%m-%d").strftime("%Y%m%d")
    save_folder = os.path.join(r"Z:\Sentinel2L1cZip",date_name)
    account_key = account_queue.get(block=True)
    [username, password] = account_infos[account_key]
    # start_date=datetime.datetime.strptime(date,"%Y-%m-%d")
    # end_date=datetime.datetime.strptime(f"{date} 23:59:59","%Y-%m-%d %H:%M:%S")
    # api_url='https://scihub.copernicus.eu/apihub/' # 无法下载历史数据
    api_url='https://scihub.copernicus.eu/dhus/'
    api = SentinelAPI(
        username,
        password,
        api_url,
        show_progressbars=True,
        timeout=None
    )
    query_kwargs = {
        'platformname': 'Sentinel-2',
        'producttype': 'S2MSI1C',
        'date': (
            datetime.datetime.strptime(date,"%Y-%m-%d"), 
            datetime.datetime.strptime(f"{date} 23:59:59", "%Y-%m-%d %H:%M:%S")
        ),
        'cloudcoverpercentage': (0, max_cloud),
        'tileid': tile,
        'raw': f'tileid:{tile} OR filename:*_T{tile}_*'
    }
    product_ids=api.query(**query_kwargs)
    download_products = [] 
    zip_paths = []
    for product_id in product_ids:
        product_info = api.get_product_odata(product_id, full=True)
        zip_name = f"{product_info['title']}.zip"
        zip_path = os.path.join(save_folder,zip_name)
        zip_paths.append(zip_path)
        if not os.path.exists(zip_path):
            download_products.append(product_id)
    api.download_all(
        download_products,
        directory_path=save_folder,
        max_attempts=max_attempts,
        n_concurrent_dl=4,
        lta_retry_delay=60*60*2,
        checksum=True,
    )     
    for zip_path in zip_paths:
        zip_import_sql(zip_path)
    account_queue.put(account_key, block=True)
=======

def ReadExcelTableNameList(excelFilePath):
    """
    获取表名名称
    :param excelFilePath:
    :return:
    """
    data = xlrd.open_workbook(excelFilePath)
    tableNameList = data.sheet_names()
    del data
    return tableNameList


def ReadExcelTableDataList(excelFilePath, tableIndex=None):
    """
    获取slx文件指定表的所有数据
    :param excelFilePath:
    :param tableIndex:
    :return:
    """
    data = xlrd.open_workbook(excelFilePath)
    if tableIndex is None:
        table = data.sheet_by_index(0)
    else:
        table = data.sheet_by_index(tableIndex)
    tableDataList = []
    rowCount = table.nrows
    for rowIndex in range(rowCount)[1:]:
        singleLineData = table.row_values(rowIndex)
        tableDataList.append(singleLineData)
    del data
    return tableDataList



def ReadExcelFileByTitleNameList(excelFilePath, goalTableTitleNameList):
    """
    获取指定表头的列数据
    :param excelFilePath:
    :param goalTableTitleNameList:
    :return:
    """
    data = xlrd.open_workbook(excelFilePath)
    sheetNameList = data.sheet_names()
    sheetName = sheetNameList[0]
    table = data.sheet_by_name(sheetName)
    tabletitleList = table.row_values(0)

    rowCount = table.nrows
    goalTableTitleColIndex = None
    newcolIndeList = []
    for listIndex, tabeltitle in enumerate(tabletitleList):
        if tabeltitle in goaltabletitleNameList:
            goalTableTitleColIndex = listIndex
            newcolIndeList.append(listIndex)
    multiplecoldataList = []
    for rowIndex in range(rowCount)[1:]:
        table.row_values(rowIndex, start_colx=newcolIndeList, )
    # for cloIndex in newcolIndeList:
    #     goalList = table.col_values(cloIndex, start_rowx=1, end_rowx=None)
    #     table.row_values()
    #     multiplecoldataList.append(goalList)
    return multiplecoldataList


def WriteExcelFile(excelFilePath, data, sheetName="sheet"):
    excelFile = xlwt.Workbook()
    sheet = excelFile.add_sheet(sheetName, cell_overwrite_ok=True)
    style = xlwt.XFStyle()  # 创建样式
    align = xlwt.Alignment()  # 创建对齐方式
    align.horz = 2  # 1-->左对齐，2-->居中，3-->右对齐
    style.alignment = align
    for rowIndex, singlerow in enumerate(data):
        for colIndex, singlecol in enumerate(singlerow):
            sheet.write(rowIndex, colIndex, singlecol, style=style)
    excelFile.save(excelFilePath)
>>>>>>> a6bcafb76f9e49e1534bc9f987efe45afb90b5aa
    return

# get sentinel2 tile list
def get_province_tiles(province):
	con = psycopg2.connect(
	database="sentinel2",
	user="postgres",
	password="12345",
	host="192.168.2.172",
	port="5432"
	)
	cur = con.cursor()
	sql = f"""
		SELECT "中国有效哨兵格网分布wgs84".mgrs
		FROM china_sy
		JOIN "中国有效哨兵格网分布wgs84" 
		on st_intersects(china_sy.geom,"中国有效哨兵格网分布wgs84".geom)
		WHERE  china_sy.qhmc = '{province}' 
	"""
	cur.execute(sql)
	con.commit()
	tiles=[record[0] for record in cur.fetchall()]
	tiles = [tile.upper() for tile in tiles]
	cur.close()
	con.close()
	return tiles

# get sentinel2 tile list
def get_city_tiles(province, city):
    con = psycopg2.connect(
    database="sentinel2",
    user="postgres",
    password="12345",
    host="192.168.2.172",
    port="5432"
    )
    cur = con.cursor()
    sql = f"""
    SELECT "中国有效哨兵格网分布wgs84".mgrs
    FROM china_dsy
    JOIN "中国有效哨兵格网分布wgs84" 
    on st_intersects(china_dsy.geom,"中国有效哨兵格网分布wgs84".geom)
    WHERE  china_dsy.qhmc = '{city}' 
    AND sjxzqdm in (
        SELECT qhdm
        FROM china_sy
        WHERE  china_sy.qhmc = '{province}' 
    )
    """
    cur.execute(sql)
    con.commit()
    tiles=[record[0] for record in cur.fetchall()]
    tiles = [tile.upper() for tile in tiles]
    cur.close()
    con.close()
    return tiles

# get sentinel2 tile list
def get_county_tiles(province, city, county):
    con = psycopg2.connect(
    database="sentinel2",
    user="postgres",
    password="12345",
    host="192.168.2.172",
    port="5432"
    )
    cur = con.cursor()
    sql = f"""
    SELECT "中国有效哨兵格网分布wgs84".mgrs
    FROM china_xy
    JOIN "中国有效哨兵格网分布wgs84" 
    on st_intersects(china_xy.geom,"中国有效哨兵格网分布wgs84".geom)
    WHERE  china_xy.qhmc = '{county}' 
    AND sjxzqdm in (
        SELECT qhdm
        FROM china_dsy
        WHERE qhmc = '{city}'
        AND sjxzqdm in (
            SELECT qhdm
            FROM china_sy
            WHERE china_sy.qhmc = '{province}'
        )  
    )
    """
    cur.execute(sql)
    con.commit()
    tiles=[record[0] for record in cur.fetchall()]
    tiles = [tile.upper() for tile in tiles]
    cur.close()
    con.close()
    return tiles

# 启动下载
def main(args):
    [tiles,dates,max_cloud,max_attempts,num_workers]=args
    account_count = len(account_infos)
    account_queue = Queue(maxsize=account_count)
    for i in range(account_count):
        account_queue.put(i,block=True)
    args_list = []
    for tile in tiles:
        for date in dates:
            args = [tile,date,max_cloud,max_attempts]
            args_list.append(args)
    pool=threadpool.ThreadPool(num_workers=num_workers)
    requests=threadpool.makeRequests(sentinel2_downloader,args_list)
    [pool.putRequest(req) for req in requests] 
    pool.wait()
    return
	

if __name__ == "__main__":
    command=r"net use Z: \\192.168.2.172\root DELL123dell /user:root"
    os.system(command)
    tilestr = "43SED-43SFD-43SGD-43SFC-43SGC-44SKJ-44SKG-44SLG-44SMG-44SNF-44TLL-44TML-44TNL-44TPL-44TQL-44TLK-44TMK-44TNK-45SUC-45TTF-45TUF-45TVF-45TWF-44SLJ"	
    tiles = tilestr.split("-")
    year = "2021"
    month = "4"
    dates = [f"{year}-{month}-{i}" for i in range(1,1+30)]
    max_cloud=80
    max_attempts=11
    num_workers=5
    args = [tiles,dates,max_cloud,max_attempts,num_workers]
    # delete_incomplete([tiles, start_date, end_date, save_folder])
    main(args)